<?php
namespace App\Common;

use PhalApi\Model\NotORMModel as NotORM;
use PhalApi\Exception\InternalServerErrorException;

/**
 * @name 重写NotORMModel
 * @author CabbageRun 2017-11-11
 * 
 */

class MyNotORM extends NotORM
{
    private $db_alias;
    private $db_join = '';

    /**
     * 原生SQL语句执行
     * @param unknown $sql
     * @param array $params
     * @return unknown
     */
    public function db_query($sql, $params = array())
    {
        $res = $this->getORM()->queryAll($sql, $params);
        return $res;
    }

    /**
     * 获取数据
     * @param string $field
     * @param array $where
     * @param string $type
     * @param string $order
     */
    public function db_get($field = '*', $where = array(), $type = 'row', $group = '', $order = '', $limit = array()){
        $res = array();
        switch($type){
            case 'one':
                $res = $this->getORM()->where($where)->group($group)->order($order)->fetchOne($field);
            break;
            case 'row':
                $res = $this->getORM()->select($field)->where($where)->group($group)->order($order)->fetchRow();
            break;
            case 'all':
                if($limit){
                    $res = $this->getORM()->select($field)->where($where)->group($group)->order($order)->limit($limit[0], $limit[1])->fetchAll();
                }else{
                    $res = $this->getORM()->select($field)->where($where)->group($group)->order($order)->fetchAll();
                }
            break;
            default:
                throw new InternalServerErrorException(\PhalApi\T('select type does not exist'));
            break;
        }
        return $res;
    }

    /**
     * 获取键值对
     * @param unknown $key
     * @param string $value
     * @param array $where
     * @param string $group
     * @param string $order
     * @param array $limit
     */
    public function db_pairs($index, $column = '', $where = array(), $group = '', $order = '', $limit = array())
    {
        if($limit){
            $res = $this->getORM()->where($where)->group($group)->order($order)->limit($limit[0], $limit[1])->fetchPairs($index, $column);
        }else{
            $res = $this->getORM()->where($where)->group($group)->order($order)->fetchPairs($index, $column);
        }
        return $res;
    }

    /**
     * 主表别名
     * @param string $alias_name
     */
    public function db_alias($alias_name = '')
    {
        $this->db_join = '';
        $this->db_alias =  $alias_name ? ' '.$alias_name.' ' : '';
    }

    /**
     * 连表查询设置
     * @param unknown $join
     * @param string $relation
     * @throws InternalServerErrorException
     */
    public function db_join($join, $relation = 'LEFT', $prefix = MYSQL_TABLES_PREFIX)
    {
        if(! $join){
            throw new InternalServerErrorException(\PhalApi\T('join tables should not be empty'));
        }
        $this->db_join .= ' '.$relation.' JOIN '.$prefix.$join.' ';
    }

    /**
     * 连表子查询设置
     * $this->db_join_mini('userid', 'user', 'u', 'GROUP BY userid', 'o.fromuserid=u.userid');
     * @param string $field
     * @param string $table
     * @param string $alias
     * @param string $where
     * @param string $on
     * @param string $relation
     * @param string $prefix
     * @throws InternalServerErrorException
     */
    public function db_left_join_mini($field = '*', $table = '', $alias = '', $where = '', $on = '', $prefix = MYSQL_TABLES_PREFIX)
    {
        if(! $field || ! $table || ! $alias || ! $on){
            throw new InternalServerErrorException(\PhalApi\T('join tables should not be empty'));
        }
        $this->db_join .= ' LEFT JOIN(SELECT '.$field.' FROM '.$prefix.$table.' '.$where.') '.$alias.' ON '.$on.' ';
    }

    /**
     * 联表获取数据条目数
     * @param string $field
     * @param array $where
     * @param string $group
     */
    public function db_join_count($field = '*', $where = array(), $group = '')
    {
        $where_str = $where_arr = $where_in = array();
        foreach($where as $key => $value){
            $where_str[$key] = $key;
            if(is_array($value)){
                if(strpos($key, '?') > 0){
                    foreach($value as $k => $v){
                        array_push($where_arr, $v);
                    }
                }else{
                    $where_str[$key] = $key.' IN('.implode(',', $value).')';
                }
            }else{
                $where_arr[] = $value;
            }
        }
        $table_name = MYSQL_TABLES_PREFIX.$this->getTableName().$this->db_alias;
        $sql = 'SELECT COUNT('.$field.') AS CNT FROM '.$table_name;
        $sql .= $this->db_join;
        $where_str && $sql .= ' WHERE '.implode(' AND ', $where_str);
        $group && $sql .= ' GROUP BY '.$group.' ';
        $rows = $this->getORM()->queryRow($sql, $where_arr);
        $count = isset($rows['CNT']) ? $rows['CNT'] : '0';
        return $count;
    }

    /**
     * 联表查询数据
     * @param string $field
     * @param array $where
     * @param string $type
     * @param string $group
     * @param string $order
     * @param string $limit
     * @throws InternalServerErrorException
     */
    public function db_join_get($field = '*', $where = array(), $type = 'row', $group = '', $order = '', $limit = array(), $index = '')
    {
        $where_str = $where_arr = $where_in = array();
        foreach($where as $key => $value){
            $where_str[$key] = $key;
            if(is_array($value)){
                if(strpos($key, '?') > 0){
                    foreach($value as $k => $v){
                        array_push($where_arr, $v);
                    }
                }else{
                    $where_str[$key] = $key.' IN('.implode(',', $value).')';
                }
            }else{
                $where_arr[] = $value;
            }
        }
        $table_name = MYSQL_TABLES_PREFIX.$this->getTableName().$this->db_alias;
        $sql = 'SELECT '.$field.' FROM '.$table_name;
        $sql .= $this->db_join;
        $where_str && $sql .= ' WHERE '.implode(' AND ', $where_str);
        $group && $sql .= ' GROUP BY '.$group.' ';
        $order && $sql .= ' ORDER BY '.$order.' ';
        $limit && $sql .= ' LIMIT '.implode(',', $limit).' ';
        $rows = array();
        switch($type){
            case 'one':
                $rows = $this->getORM()->queryRow($sql, $where_arr);
                $pos = strpos($field, '.');
                $flag = $pos ? substr($field, ($pos + 1)) : $field;
                $rows = $rows[$flag];
            break;
            case 'row':
                $rows = $this->getORM()->queryRow($sql, $where_arr);
            break;
            case 'all':
                $rows = $this->getORM()->queryRows($sql, $where_arr);
            break;
            default:
                throw new InternalServerErrorException(\PhalApi\T('select type does not exist'));
            break;
        }
        if(is_array($rows) && $index){
            $opr_rows = array();
            foreach($rows as $key => $value){
                $opr_rows[$value[$index]] = $value;
            }
            unset($rows);
            return $opr_rows;
        }
        return $rows;
    }

    /**
     * 统计
     * @param array $where
     * @return unknown
     */
    public function db_count($id = '*',$where = array())
    {
        $res = $this->getORM()->where($where)->count($id);
        return $res;
    }

    /**
     * 求和
     * @param array $where
     * @return unknown
     */
    public function db_sum($id, $where = array())
    {
        $res = $this->getORM()->where($where)->sum($id);
        return $res;
    }

    /**
     * 插入
     * {@inheritDoc}
     * @see \PhalApi\Model\NotORMModel::insert()
     */
    public function db_insert($data, $multi = 0)
    {
        if($multi == 1){
            $res = $this->getORM()->insert_multi($data);
            return $res;
        }else{
            $res = $this->getORM()->insert($data);
            $id = $this->getORM()->insert_id();
            return $id;
        }
    }


    /**
     * 更新
     * {@inheritDoc}
     * @see \PhalApi\Model\NotORMModel::update()
     */
    public function db_update($data, $where = array())
    {
        $res = $this->getORM()->where($where)->update($data);
        return $res;
    }

    /**
     * 删除
     * @param array $where
     * @return unknown
     */
    public function db_delete($where = array())
    {
        $res = $this->getORM()->where($where)->delete();
        return $res;
    }

    /**
     * 最大值
     * @param unknown $field
     * @return unknown
     */
    public function db_max($field)
    {
        $res = $this->getORM()->max($field);
        return $res;
    }
}
